﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ServiceThuVien
{
    public class SQLServerInsert
    {
        // string conn = ConfigurationSettings.AppSettings["conn"].ToString();
        SqlConnection cn;
        public SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();


        //private static string GetConnectionString()
        //{
        //    // To avoid storing the connection string in your code,
        //    // you can retrieve it from a configuration file. 
        //    return "Server=RADO-PC\\SQLEXPRESS;Integrated Security=SSPI;" +
        //        "Initial Catalog=QLTV";
        //}

        private string GetConnection()
        {
            builder.ConnectionString = "server=RADO-PC\\SQLEXPRESS;user id=sa;" +
            "password= 123;initial catalog=QLTV";
            return builder.ConnectionString;
        }

        private void ConnectSQL(ref SqlConnection cn, string pName, ref SqlCommand cmd)
        {
            cn = new SqlConnection(GetConnection());
            string procName = pName;
            cmd = new SqlCommand();
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = cn;
        }

        #region Insert

        //Insert CT Tac Gia
        public int InsertCTTacGia(string MaTacGia, string MaDauSach)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertCTTacGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaTacGia", MaTacGia);
            cmd.Parameters.AddWithValue("@MaDauSach", MaDauSach);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Insert Doc Gia
        public int InsertDocGia(string MaLoaiDocGia, string HoTenDocGia, DateTime NgaySinh, string DiaChi, string Email, DateTime NgayLapThe, DateTime NgayHetHan, int TienNo, string MatKhau, int DoiMatKhau, int SoSachDangMuon)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertDocGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaLoaiDocGia", MaLoaiDocGia);
            cmd.Parameters.AddWithValue("@HoTenDocGia", HoTenDocGia);
            cmd.Parameters.AddWithValue("@NgaySinh", NgaySinh);
            cmd.Parameters.AddWithValue("@DiaChi", DiaChi);
            cmd.Parameters.AddWithValue("@Email", Email);
            cmd.Parameters.AddWithValue("@NgayLapThe", NgayLapThe);
            cmd.Parameters.AddWithValue("@NgayHetHan", NgayHetHan);
            cmd.Parameters.AddWithValue("@TienNo", TienNo);
            cmd.Parameters.AddWithValue("@MatKhau", MatKhau);
            cmd.Parameters.AddWithValue("@DoiMatKhau", DoiMatKhau);
            cmd.Parameters.AddWithValue("@SoSachDangMuon", SoSachDangMuon);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Insert Loai Doc Gia
        public int InsertLoaiDocGia(string LoaiDocGia)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertLoaiDocGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@TenLoaiDocGia", LoaiDocGia);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Insert The Loai
        public int InsertTheLoai(string TenTheLoai)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertTheLoai";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@TenTheLoai", TenTheLoai);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Insert Dau Sach
        public int InsertDauSach(string TenDauSach, string MaTheLoai)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertDauSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@TenDauSach", TenDauSach);
            cmd.Parameters.AddWithValue("@MaTheLoai", MaTheLoai);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Insert Sach
        public int InsertSach(string MaDauSach, int NamXuatBan, string NhaXuatBan, int SoLuong, int DonGia)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDauSach", MaDauSach);
            cmd.Parameters.AddWithValue("@NamXuatBan", NamXuatBan);
            cmd.Parameters.AddWithValue("@NhaXuatBan", NhaXuatBan);
            cmd.Parameters.AddWithValue("@SoLuong", SoLuong);
            cmd.Parameters.AddWithValue("@DonGia", DonGia);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Insert cuon sach
        public int InsertCuonSach(string MaSach, int TinhTrang)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertCuonSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaSach", MaSach);
            cmd.Parameters.AddWithValue("@TinhTrang", TinhTrang);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Insert Tac gia
        public int InsertTacGia(string HoTenTacGia, string DiaChi)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertTacGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            //cmd.Parameters.Add(new SqlParameter("@HoTenTacGia", HoTenTacGia));
            //cmd.Parameters.Add(new SqlParameter("@DiaChi", DiaChi));
            cmd.Parameters.AddWithValue("@HoTenTacGia", HoTenTacGia);
            cmd.Parameters.AddWithValue("@DiaChi", DiaChi);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }


        //Change here <=============================================================
        //Insert Phieu Nhap
        public string InsertPhieuNhap(DateTime NgayLap, int TongTien)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertPhieuNhap";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@NgayLap", NgayLap);
            cmd.Parameters.AddWithValue("@TongTien", TongTien);

            DataSet ds = new DataSet();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);               
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        //Insert Phieu Nhap
        public int InsertCTPhieuNhap(string MaPhieuNhap, string MaSach, int SoLuongNhap, int ThanhTien)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertCTPhieuNhap";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuNhap", MaPhieuNhap);
            cmd.Parameters.AddWithValue("@MaSach", MaSach);
            cmd.Parameters.AddWithValue("@SoLuongNhap", SoLuongNhap);
            cmd.Parameters.AddWithValue("@ThanhTien", ThanhTien);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Insert Phieu Nhap
        public string InsertBCMuonSach(int Thang, int Nam, int TongLuotMuon)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertBCMuonSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);
            
            cmd.Parameters.AddWithValue("@Thang", Thang);
            cmd.Parameters.AddWithValue("@Nam", Nam);
            cmd.Parameters.AddWithValue("@TongLuotMuon", TongLuotMuon);

            DataSet ds = new DataSet();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        //Insert Phieu Nhap
        public int InsertBCTraTre(DateTime Ngay, string MaCuonSach, DateTime NgayMuon, int SoNgayTraTre)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertBCTraTre";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@Ngay", Ngay);
            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);
            cmd.Parameters.AddWithValue("@NgayMuon", NgayMuon);
            cmd.Parameters.AddWithValue("@SoNgayTraTre", SoNgayTraTre);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int InsertCTBCMuonSach(string MaBCMS, string MaTheLoai, int LuotMuon, float TiLe)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertCTBCMuonSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaBCMS", MaBCMS);
            cmd.Parameters.AddWithValue("@MaTheLoai", MaTheLoai);
            cmd.Parameters.AddWithValue("@LuotMuon", LuotMuon);
            cmd.Parameters.AddWithValue("@TiLe", TiLe);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int InsertCTPhieuMuon(string MaCuonSach, string MaPhieuMuon, int TinhTrangPhieu)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertCTPhieuMuon";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);
            cmd.Parameters.AddWithValue("@MaPhieuMuon", MaPhieuMuon);
            cmd.Parameters.AddWithValue("@TinhTrangPhieu", TinhTrangPhieu);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public int InsertCTPhieuTra(string MaPhieuTra, string MaCuonSach, int TienPhat, DateTime NgayMuon, int SoNgayMuon, string MaPhieuMuon)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertCTPhieuTra";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuTra", MaPhieuTra);
            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);
            cmd.Parameters.AddWithValue("@TienPhat", TienPhat);
            cmd.Parameters.AddWithValue("@NgayMuon", NgayMuon);
            cmd.Parameters.AddWithValue("@SoNgayMuon", SoNgayMuon);
            cmd.Parameters.AddWithValue("@MaPhieuMuon", MaPhieuMuon);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        public string InsertPhieuMuon(string MaDocGia, DateTime NgayMuon, DateTime NgayPhaiTra, int SoSachMuon)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertPhieuMuon";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@NgayMuon", NgayMuon);
            cmd.Parameters.AddWithValue("@NgayPhaiTra", NgayPhaiTra);
            cmd.Parameters.AddWithValue("@SoSachMuon", SoSachMuon);

            DataSet ds = new DataSet();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public string InsertPhieuTra(string MaDocGia, DateTime NgayTra, int TienPhatKyNay, int SoTienTra)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertPhieuTra";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@NgayTra", NgayTra);
            cmd.Parameters.AddWithValue("@TienPhatKyNay", TienPhatKyNay);
            cmd.Parameters.AddWithValue("@SoTienTra", SoTienTra);

            DataSet ds = new DataSet();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        public string InsertPhieuPhat(string MaDocGia, DateTime NgayThu, int TienNo, int SoTienThu, int ConLai)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertPhieuPhat";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);
            
            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);
            cmd.Parameters.AddWithValue("@NgayThu", NgayThu);
            cmd.Parameters.AddWithValue("@TienNo", TienNo);
            cmd.Parameters.AddWithValue("@SoTienThu", SoTienThu);
            cmd.Parameters.AddWithValue("@ConLai", ConLai);

            DataSet ds = new DataSet();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                cn.Close();
                return temp;
            }
            catch
            {
                return null;
            }
        }

        //Insert NhanVien
        public int InsertNhanVien(string MaLoaiNhanVien, string HoTenNhanVien, string MatKhau, int DoiMatKhau)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertNhanVien";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaLoaiNhanVien", MaLoaiNhanVien);
            cmd.Parameters.AddWithValue("@HoTenNhanVien", HoTenNhanVien);
            cmd.Parameters.AddWithValue("@MatKhau", MatKhau);
            cmd.Parameters.AddWithValue("@DoiMatKhau", DoiMatKhau);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Insert Loai Doc Gia
        public int InsertLoaiNhanVien(string TenLoaiNhanVien)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "InsertLoaiNhanVien";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@TenLoaiNhanVien", TenLoaiNhanVien);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }
        #endregion   
    }
}